create database duypham;
go
use duypham;
go
create table tblKhoa(
khoaMa int primary key,
khoaTen varchar(30)
)
go
create table tblLop_Ql(
lqlMa int primary key,
lqlTen varchar(30),
khoaMa int constraint FK_1 foreign key references tblKhoa(khoaMa)
)
go
create table tblMonhoc(
mhMa int primary key,
mhTen varchar(30),
mhGio varchar(30),
mhGv varchar(30)
)
go
create table tbllopMh(
lmhMa int primary key,
lmhTen varchar(40)
)
go
create table tbllh_daotao(
dtMa int primary key,
dtTen varchar(30)
)
go
create table tblkihoc(
kiMa int primary key,
kiTen varchar(30),
kiNam varchar(30)
)
go
create table tblquyen(
qId int primary key,
qTen varchar(30)
)
create table tblsinh_vien(
svMa int primary key,
svTen varchar(30),
svBir datetime,
lqlMa int constraint FK_2 foreign key references tblLop_Ql(lqlMa),
svEmail varchar(30),
deMa int constraint FK_3 foreign key references tbllh_daotao(dtMa)
)
create table tblSV_LMH(
lmhMa int constraint FK_4 foreign key references tbllopMh(lmhMa ),
svMa int constraint FK_5 foreign key references tblsinh_vien(svMa )
)
go
create table tblMH_LMH(
lmhMa int constraint FK_6 foreign key references tbllopMh(lmhMa ),
mhMa int constraint FK_7 foreign key references tblMonhoc(mhMa  )
)
create table tblNguoi_dung(
ndMa int primary key,
ndTen varchar(30),
ndBir datetime,
ndDN varchar(30),
ngPass varchar(40),
qId int constraint FK_23 foreign key references  tblquyen(qId)
)
go
create table tblPhat_tien(
ptMA int primary key,
svMa int constraint FK_8 foreign key references tblsinh_vien(svMa),
mhMa int constraint FK_9 foreign key references tblMonhoc(mhMa),
ptNghi int,
ptTien money
)
create table tblLich_day(
dMa int primary key,
kiMa int constraint fk_10 foreign key references tblkihoc(kiMa),
mhMa int constraint FK_11 foreign key references tblMonhoc(mhMa),
ndMa int constraint FK_12 foreign key references tblNguoi_dung(ndMa),
dStart varchar(30),
dEnd varchar(35)
)
go
create table tblDay_monhoc(
dMa int constraint fk_13 foreign key references tblLich_day(dMa),
mhMa int constraint FK_14 foreign key references tblMonhoc(mhMa),
)